package com.guagua.chat.statistics;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;

import com.guagua.base.database.ConnectionManager;
import com.guagua.chat.base.BaseDao;
import com.guagua.chat.topic.TopicDao;
/**
 * 登录计数、统计积分 DAO
 * @author liyi
 *
 */
public class StatisticsDao extends BaseDao {
	
	
	private static Logger log = Logger.getLogger(StatisticsDao.class);
	
	private static boolean isExecByDAY;//是否正在执行按天统计
	/**
	 * 执行统计功能
	 * @param startTime 范围开始时间
	 * @param endTime   范围结束时间
	 * @param year      
	 * @param month
	 * @param day
	 * @param isLastDay 是否月末
	 * @param next_month_year 下个月年
	 * @param next_month 下个月
	 */
	public void execStatisticsByDay(long startTime,long endTime,int year,int month,int day,boolean isLastDay,int next_month_year,int next_month){
		Connection con = null;
		Connection con2 = null; 
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		try {
			if(isLastDay){
				createNextMonthLoginTable(next_month_year,next_month);
			}
			isExecByDAY = true;
			String sql = "select r.room_id room_id,r.name room_name,dbo.fr_get_category_root_id(m.category_id) root_id from t_chatroom r,t_room_category_map m with(nolock) where r.room_id=m.room_id and m.is_main_path=1";
			con = ConnectionManager.getDataSourceConnection(CHATROOM_JNDI_NAME);
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			con2  = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			while(rs.next()){
				long roomId =rs.getLong("room_id");
				String roomName = rs.getString("room_name");
				long rootId = rs.getLong("root_id");
				try{//执行存储过程
					log.debug("{call proc_statistics_room_day "+roomId+",'"+roomName+"',"+rootId+","+startTime+","+endTime+","+year+","+month+","+day+"}");
//					pstmt2 = con2.prepareStatement("{call proc_statistics_room_day "+roomId+",'"+roomName+"',"+rootId+","+startTime+","+endTime+","+year+","+month+","+day+"}");
					pstmt2 = con2.prepareStatement("{call proc_statistics_room_day ?,?,?,?,?,?,?,?}");
					pstmt2.setLong(1, roomId);
					pstmt2.setString(2, roomName);
					pstmt2.setLong(3, rootId);
					pstmt2.setLong(4, startTime);
					pstmt2.setLong(5, endTime);
					pstmt2.setInt(6, year);
					pstmt2.setInt(7, month);
					pstmt2.setInt(8, day);
					pstmt2.execute();
					
				}catch (Exception e) {
					log.error(e.getMessage(), e);
				}finally{
					pstmt2.close();
				}
			}
			
			
			
			
			
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		} finally{
			//关闭连接
			ConnectionManager.close(rs, pstmt, con);
			try{
				if(con2 !=null && !con2.isClosed()){
					con2.close();
				}
			}catch (Exception e) {
				
			}
			isExecByDAY = false;
		}
		
		
	}
	private void createNextMonthLoginTable(int next_month_year,int next_month){
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			String month = next_month+"";
			if(next_month<10){
				month = "0"+next_month;
			}
			con = ConnectionManager.getDataSourceConnection(JNDI_NAME);
			String createSQL = "CREATE TABLE [dbo].[t_login_log_"+next_month_year+"_"+month+"]([room_id] [bigint] NOT NULL,[user_id] [bigint] NOT NULL,[ip] [varchar](20) NULL,[op_time] [bigint] NOT NULL, CONSTRAINT [PK_t_login_log_"+next_month_year+"_"+month+"] PRIMARY KEY CLUSTERED ([room_id] ASC,[user_id] ASC,[op_time] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] ";
			pstmt = con.prepareStatement(createSQL);
			pstmt.execute();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}finally{
			//关闭连接
			ConnectionManager.close(null, pstmt, con);
			
		}
	}
	public void execStatisticsByMonth(int year,int month){
		Connection con = null;
		Connection con2 = null; 
		PreparedStatement pstmt = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs = null;
		try {
			while(!isExecByDAY){//isExecByDAY == true表示按天统计正在执行
				String sql = "select r.room_id room_id,r.name room_name,dbo.fr_get_category_root_id(m.category_id) root_id,m.category_id category_id from t_chatroom r,t_room_category_map m where r.room_id=m.room_id and m.is_main_path=1";
				con = ConnectionManager.getDataSourceConnection(CHATROOM_JNDI_NAME);
				pstmt = con.prepareStatement(sql);
				rs = pstmt.executeQuery();
				con2  = ConnectionManager.getDataSourceConnection(JNDI_NAME);
				while(rs.next()){
					long roomId =rs.getLong("room_id");
					String roomName = rs.getString("room_name");
					long rootId = rs.getLong("root_id");
//					long categoryId = rs.getLong("category_id");
					ResultSet rs2 = null;
					try{
						String sql1 ="select sum(point) points ,sum(login_count) logincounts,sum(topic_count) topiccounts,sum(posts_count) postscounts,sum(join_count) joincounts,sum(active_online) activeonlines from t_room_point_day where room_id=?and year=? and month=? and category_id=?";
						pstmt2 = con2.prepareStatement(sql1);
						pstmt2.setLong(1, roomId);
						pstmt2.setLong(2, year);
						pstmt2.setLong(3, month);
						pstmt2.setLong(4, rootId);
						rs2 = pstmt2.executeQuery();
						if(rs2.next()){
							rs2.getLong("points");
							rs2.getLong("logincounts");
							rs2.getLong("topiccounts");
							rs2.getLong("postscounts");
							rs2.getLong("joincounts");
							rs2.getLong("activeonlines");
							
							String sql2 ="insert into t_room_point_month(year,month,room_id,room_name,category_id,point,topic_count,posts_count,login_count,join_count,active_online) ";
							sql2+="values(?,?,?,?,?,?,?,?,?,?,?)";
							pstmt2 = con2.prepareStatement(sql2);
							pstmt2.setInt(1, year);
							pstmt2.setInt(2, month);
							pstmt2.setLong(3, roomId);
							pstmt2.setString(4,roomName);
							pstmt2.setLong(5,rootId);
							
							pstmt2.setLong(6,rs2.getLong("points"));
							pstmt2.setLong(7,rs2.getLong("topiccounts"));
							pstmt2.setLong(8,rs2.getLong("postscounts"));
							pstmt2.setLong(9,rs2.getLong("logincounts"));
							pstmt2.setLong(10,rs2.getLong("joincounts"));
					
							pstmt2.setLong(11,rs2.getLong("activeonlines"));
							pstmt2.execute();
						}
						
						
					}catch (Exception e) {
						log.error(e.getMessage(), e);
					}finally{
						pstmt2.close();
						if(rs2 != null){
							rs2.close();
						}
					}
				}
				break;
			}
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		} finally{
			//关闭连接
			ConnectionManager.close(rs, pstmt, con);
			try{
				if(con2 !=null && !con2.isClosed()){
					con2.close();
				}
			}catch (Exception e) {
				
			}
			isExecByDAY = false;
		}
	}
	
}
